/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */



import java.sql.*;

/**
 *
 * @author dimitri.tiago
 */
public class TestData
{
    private static Connection conn;

    static final String JDBC_DRIVER = "com.intersys.jdbc.CacheDriver";
    static final String DATABASE_URL = "jdbc:Cache://localhost:60601/INSE6260";
    static final String USERNAME = "_SYSTEM";
    static final String PASSWORD = "bsobso";

    public static void main(String args[])
    {
        // establish database connection
        try
        {
            // set up connection
            Class.forName (JDBC_DRIVER);

            conn = DriverManager.getConnection(DATABASE_URL, USERNAME, PASSWORD);

            // clear database tables
            Statement stmt = conn.createStatement();

            stmt.executeUpdate("DELETE FROM atms.BankClient");
            stmt.executeUpdate("DELETE FROM atms.BankAccount");
            stmt.executeUpdate("DELETE FROM atms.TransactionLineItem");

            // insert bank clients and accounts

            // bank client with 4 accounts
            stmt.executeUpdate("INSERT INTO atms.BankClient(blocked, cardNumber, dailyTransactionLimit, maxOneTimeWithAmt, pin) "
                    + "VALUES (0, 111111, 1000, 500, 1111)");
            stmt.executeUpdate("INSERT INTO atms.BankAccount(accountNumber, accountType, balance, cardNumber) "
                + "VALUES (1, 'Checking', 1000, 111111)");
            stmt.executeUpdate("INSERT INTO atms.BankAccount(accountNumber, accountType, balance, cardNumber) "
                + "VALUES (2, 'Savings', 1000, 111111)");
            stmt.executeUpdate("INSERT INTO atms.BankAccount(accountNumber, accountType, balance, cardNumber) "
                + "VALUES (3, 'Balanced Fund', 1000, 111111)");
            stmt.executeUpdate("INSERT INTO atms.BankAccount(accountNumber, accountType, balance, cardNumber) "
                + "VALUES (4, 'Money Markets', 1000, 111111)");

            // bank client with two accounts
            stmt.executeUpdate("INSERT INTO atms.BankClient(blocked, cardNumber, dailyTransactionLimit, maxOneTimeWithAmt, pin) "
                    + "VALUES (0, 222222, 1000, 500, 2222)");
            stmt.executeUpdate("INSERT INTO atms.BankAccount(accountNumber, accountType, balance, cardNumber) "
                + "VALUES (5, 'Checking', 1000, 222222)");
            stmt.executeUpdate("INSERT INTO atms.BankAccount(accountNumber, accountType, balance, cardNumber) "
                + "VALUES (6, 'Savings', 1000, 222222)");

            // bank client with no money in accounts
            stmt.executeUpdate("INSERT INTO atms.BankClient(blocked, cardNumber, dailyTransactionLimit, maxOneTimeWithAmt, pin) "
                    + "VALUES (0, 333333, 1000, 500, 3333)");
            stmt.executeUpdate("INSERT INTO atms.BankAccount(accountNumber, accountType, balance, cardNumber) "
                + "VALUES (7, 'Checking', 0, 333333)");
            stmt.executeUpdate("INSERT INTO atms.BankAccount(accountNumber, accountType, balance, cardNumber) "
                + "VALUES (8, 'Savings', 0, 333333)");

            // acceptance test user 1
            stmt.executeUpdate("INSERT INTO atms.BankClient(blocked, cardNumber, dailyTransactionLimit, maxOneTimeWithAmt, pin) "
                    + "VALUES (0, 123456, 200, 500, 1234)");
            stmt.executeUpdate("INSERT INTO atms.BankAccount(accountNumber, accountType, balance, cardNumber) "
                + "VALUES (9, 'Checking', 750, 123456)");

            // acceptance test user 2
            stmt.executeUpdate("INSERT INTO atms.BankClient(blocked, cardNumber, dailyTransactionLimit, maxOneTimeWithAmt, pin) "
                    + "VALUES (0, 234567, 1000, 500, 2345)");
            stmt.executeUpdate("INSERT INTO atms.BankAccount(accountNumber, accountType, balance, cardNumber) "
                + "VALUES (10, 'Checking', 450, 234567)");
            stmt.executeUpdate("INSERT INTO atms.BankAccount(accountNumber, accountType, balance, cardNumber) "
                + "VALUES (11, 'Savings', 100, 234567)");

            // acceptance test user 3
            stmt.executeUpdate("INSERT INTO atms.BankClient(blocked, cardNumber, dailyTransactionLimit, maxOneTimeWithAmt, pin) "
                    + "VALUES (0, 345678, 1000, 200, 3456)");
            stmt.executeUpdate("INSERT INTO atms.BankAccount(accountNumber, accountType, balance, cardNumber) "
                + "VALUES (12, 'Checking', 750, 345678)");
            stmt.executeUpdate("INSERT INTO atms.BankAccount(accountNumber, accountType, balance, cardNumber) "
                + "VALUES (13, 'Savings', 0, 345678)");

            // bank client with no accounts
            stmt.executeUpdate("INSERT INTO atms.BankClient(blocked, cardNumber, dailyTransactionLimit, maxOneTimeWithAmt, pin) "
                    + "VALUES (0, 444444, 1000, 500, 4444)");

            // close statement and connection
            stmt.close();
            conn.close();
        }
        catch(ClassNotFoundException classNotFoundException)
        {
            classNotFoundException.printStackTrace();
            System.exit(1);
        }
        catch(SQLException sqlException)
        {
            sqlException.printStackTrace();
            System.exit(1);
        }
    }
}
